﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Common;

using System.Data.OleDb;

namespace DAL
{
    public class Account
    {

        public bool IsAccountExist(string Account)
        {
            CIMDataContext db = new CIMDataContext();

            if (db.Sys_Account.Count(u => u.State == 0 && u.Account == Account.Trim()) > 0)
                return true;
            else
                return false;
        }

        public Sys_Account GetAccountByKey(int UserKey)
        {
            CIMDataContext db = new CIMDataContext();

            try
            {
                return db.Sys_Account.Single(u => u.UserKey == UserKey && u.State == 0);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public Sys_Account GetAccountByAccount(string Account)
        {
            CIMDataContext db = new CIMDataContext();

            try
            {
                return db.Sys_Account.Single(u => u.Account == Account && u.State == 0);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public IList<Sys_Account> GetAccount(int UserType)
        {
            CIMDataContext db = new CIMDataContext();
            var Account = from account in db.Sys_Account
                          where account.UserType == UserType && account.State == 0
                          orderby account.UserKey
                          select account;
            return Account.ToList();
        }

        public IList<Sys_Account> GetNotAccount(int UserType)
        {
            CIMDataContext db = new CIMDataContext();
            var Account = from account in db.Sys_Account
                          where account.UserType != UserType && account.State == 0
                          orderby account.UserKey
                          select account;
            return Account.ToList();
        }

        public string GetAccountName(int UserKey)
        {
            CIMDataContext db = new CIMDataContext();

            string val;
            try
            {
                val = db.Sys_Account.Where(a => a.UserKey == UserKey && a.State == 0).First().RealName;
            }
            catch (InvalidOperationException ex)
            {
                val = "";
            }
            return val;
        }

        public string GetAccountCompanyName(int UserKey)
        {
            CIMDataContext db = new CIMDataContext();

            string val;
            try
            {
                val = db.Sys_Account.Where(a => a.UserKey == UserKey && a.State == 0).First().CompanyName;
            }
            catch (InvalidOperationException ex)
            {
                val = "";
            }
            return val;
        }

        public string GetAccountShortCompanyName(int UserKey)
        {
            CIMDataContext db = new CIMDataContext();

            string val;
            try
            {
                val = db.Sys_Account.Where(a => a.UserKey == UserKey && a.State == 0).First().ShortCompanyName;
            }
            catch (InvalidOperationException ex)
            {
                val = "";
            }
            return val;
        }

        public string GetAccountAllName(int UserKey)
        {
            CIMDataContext db = new CIMDataContext();

            string val;
            try
            {
                string val1 = db.Sys_Account.Where(a => a.UserKey == UserKey && a.State == 0).First().CompanyName;
                string val2 = db.Sys_Account.Where(a => a.UserKey == UserKey && a.State == 0).First().RealName;
                return val1 + "," + val2;
            }
            catch (InvalidOperationException ex)
            {
                val = "";
            }
            return val;
        }

        public string GetAccountShortAllName(int UserKey)
        {
            CIMDataContext db = new CIMDataContext();

            string val;
            try
            {
                var customer = db.Sys_Account.Where(a => a.UserKey == UserKey && a.State == 0).First();
                return customer.RealName + " (" + customer.ShortCompanyName + ")";
            }
            catch (InvalidOperationException ex)
            {
                val = "";
            }
            return val;
        }

        public string GetAccountName(object UserKey)
        {
            CIMDataContext db = new CIMDataContext();

            string val;
            try
            {
                val = db.Sys_Account.Where(a => a.UserKey == Convert.ToInt32(UserKey) && a.State == 0).First().RealName;
            }
            catch (InvalidOperationException ex)
            {
                val = "";
            }
            return val;
        }

        public static string GetAccountType(object UserType)
        {

            int usertype = (UserType == null ? 0 : Convert.ToInt32(UserType));

            using (CIMEntities en = new CIMEntities())
            {
                AccountType accounttype = en.AccountTypeSet.FirstOrDefault(a => a.TypeKey == usertype);
                if (accounttype == null)
                    return "";
                else
                    return accounttype.TypeName;
            }
        }

        //条件查询
        public IQueryable<Sys_Account> SelectAccount(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Sys_Account> Query;

            if (QueryTable == null)
            {
                Query = from a in db.Sys_Account
                        where a.State == 0
                        orderby a.UserType descending, a.UserKey descending
                        select a;
            }
            else
            {
                Query = from a in GetConditionQuery(QueryTable)
                        where a.State == 0
                        orderby a.UserType descending, a.UserKey descending
                        select a;
            }
            return Query;
        }

        private IQueryable<Sys_Account> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            var predicate = PredicateBuilder.True<Sys_Account>();

            if (QueryTable["UserType"] != null)
                predicate = predicate.And(a => a.UserType == Convert.ToInt32(QueryTable["UserType"]));
            if (QueryTable["RealName"] != null)
                predicate = predicate.And(a => a.RealName.Contains(QueryTable["RealName"].ToString().Trim()));
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And(a => a.CompanyName.Contains(QueryTable["Keyword"].ToString().Trim())
                    || a.Account.Contains(QueryTable["Keyword"].ToString().Trim())
                    || a.Address.Contains(QueryTable["Keyword"].ToString().Trim())
                    || a.CompanyName.Contains(QueryTable["Keyword"].ToString().Trim())
                    || a.ShortCompanyName.Contains(QueryTable["Keyword"].ToString().Trim())
                    || a.Email.Contains(QueryTable["Keyword"].ToString().Trim())
                    );
            return db.Sys_Account.Where(predicate);
        }

        public bool AddAccount(Sys_Account account)
        {
            CIMDataContext db = new CIMDataContext();

            try
            {
                db.Sys_Account.InsertOnSubmit(account);
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool EditAccount(Sys_Account account)
        {
            CIMDataContext db = new CIMDataContext();
            try
            {
                Sys_Account Account = (from u in db.Sys_Account
                                       where u.UserKey == account.UserKey
                                       select u).First();
                Account.UserKey = account.UserKey;
                Account.UserType = account.UserType;
                Account.Password = account.Password;
                Account.Email = account.Email;
                Account.CompanyName = account.CompanyName;
                Account.ShortCompanyName = account.ShortCompanyName;
                Account.Tel = account.Tel;
                Account.Tel2 = account.Tel2;
                Account.FAX = account.FAX;
                Account.RealName = account.RealName;
                Account.Mob = account.Mob;
                Account.Zhiwu = account.Zhiwu;
                Account.CustomerType = account.CustomerType;
                Account.XiShu = account.XiShu;
                Account.ContractNO = account.ContractNO;
                Account.Address = account.Address;

                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public void DelAccount(int UserKey)
        {
            using (CIMDataContext db = new CIMDataContext())
            {
                Sys_Account account = db.Sys_Account.Single(u => u.UserKey == UserKey);
                account.State = 1;
                db.SubmitChanges();
            }
        }

        //验证登陆
        public int CheckLogin(string Account, string Password)
        {
            using (CIMDataContext db = new CIMDataContext())
            {
                Sys_Account account = db.Sys_Account.SingleOrDefault(u => u.Account == Account && u.State == 0);
                if (account == null)
                {
                    return 0;
                }
                else
                {
                    account = db.Sys_Account.SingleOrDefault(u => u.Account == Account && u.Password == WebUtility.MD5(Password, 32) && u.State == 0);
                    if (account == null)
                    {
                        return 1;
                    }
                    else
                    {
                        return 2;
                    }
                }
            }
        }

        //是否显示
        public static string ShowForUserTypeList(string UserTypeList, object UserType)
        {
            if (UserTypeList.IndexOf(UserType.ToString()) < 0)
                return "none";
            else
                return "block";
        }

        //是否显示
        public static bool ShowForUserTypeList1(string UserTypeList, object UserType)
        {
            if (UserTypeList.IndexOf(UserType.ToString()) < 0)
                return false;
            else
                return true;
        }

        //是否显示
        public static string ShowForMenuTypeList(string MenuTypeList, string MenuType)
        {
            if (MenuTypeList.IndexOf(MenuType) < 0)
                return "none";
            else
                return "block";
        }

        //是否显示
        public static bool ShowForMenuTypeList1(string MenuTypeList, string MenuType)
        {
            if (MenuTypeList.IndexOf(MenuType) < 0)
                return false;
            else
                return true;
        }

        //导出
        public static void DataToExcel_(Hashtable QueryTable)
        {
            IQueryable<Sys_Account> Query = new Account().SelectAccount(QueryTable);

            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=export.xls");
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.Charset = "gb2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            System.IO.StringWriter writer = new System.IO.StringWriter();

            foreach (Sys_Account account in Query)
            {
                writer.Write(Account.GetAccountType(account.UserType)); writer.Write("\t");
                writer.Write(account.Account); writer.Write("\t");
                writer.Write(account.CompanyName); writer.Write("\t");
                writer.Write(account.ShortCompanyName); writer.Write("\t");
                writer.Write(account.RealName); writer.WriteLine();
            }
            HttpContext.Current.Response.Write(writer.ToString());
            HttpContext.Current.Response.End();
        }

        public static void DataToExcel(Hashtable QueryTable)
        {
            IQueryable<Sys_Account> Query = new Account().SelectAccount(QueryTable);

            WebUtility.DelExport();

            string FileName = "~/export/" + Guid.NewGuid().ToString() + ".xls";
            string sNewFullFile = HttpContext.Current.Server.MapPath(FileName);

            try
            {
                File.Copy(HttpContext.Current.Server.MapPath("~/common/template.xls"), sNewFullFile);
            }
            catch (Exception er)
            {
                HttpContext.Current.Response.Write(er.Message);
                return;
            }

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbCommand cmd = null;

            bool bRet = false;

            try
            {
                conn.Open();
                cmd = new OleDbCommand("create table [用户信息]([用户类型] Text,[用户名] Text,[所在公司] Text,[公司简称] Text,[真实姓名] Text)", conn);
                cmd.ExecuteNonQuery();


                string strSQL = "INSERT INTO [用户信息$] ([用户类型], [用户名],[所在公司],[公司简称],[真实姓名]) VALUES (?,?,?,?,?)";
                cmd = new OleDbCommand(strSQL, conn);
                for (int i = 0; i < 5; i++)
                {
                    cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
                }
                foreach (Sys_Account account in Query)
                {
                    cmd.Parameters[0].Value = string.IsNullOrEmpty(Account.GetAccountType(account.UserType)) ? "" : Account.GetAccountType(account.UserType);
                    cmd.Parameters[1].Value = string.IsNullOrEmpty(account.Account) ? "" : account.Account;
                    cmd.Parameters[2].Value = string.IsNullOrEmpty(account.CompanyName) ? "" : account.CompanyName;
                    cmd.Parameters[3].Value = string.IsNullOrEmpty(account.ShortCompanyName) ? "" : account.ShortCompanyName;
                    cmd.Parameters[4].Value = string.IsNullOrEmpty(account.RealName) ? "" : account.RealName;

                    cmd.ExecuteNonQuery();
                }
                bRet = true;
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);

            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Dispose();
            }

            if (bRet)
                HttpContext.Current.Response.Redirect(FileName);
        }
    }

}
